package com.lhz.project.wage.service.impl;


import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.lhz.common.utils.UUIDUtils;
import com.lhz.common.utils.file.FileUtil;
import com.lhz.framework.exception.ServiceException;
import com.lhz.project.wage.common.ExcelUtil;
import com.lhz.project.wage.common.WageExcelListener;
import com.lhz.project.wage.mapper.WageBaseMapper;
import com.lhz.project.wage.mapper.WageWealMapper;
import com.lhz.project.wage.model.entity.WageBase;
import com.lhz.project.wage.model.entity.WageOut;
import com.lhz.project.wage.model.entity.WageWeal;
import com.lhz.project.wage.model.excel.WageBaseData;
import com.lhz.project.wage.model.excel.WageWealData;
import com.lhz.project.wage.service.WageService;
import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author LiHuaZhi
 * @since 2020-07-04 13:05:57
 */
@Service

public class WageServiceImpl implements WageService {

    @Resource
    private WageWealMapper wageWealMapper;
    @Resource
    WageBaseMapper wageBaseMapper;

    @Resource
    private WageExcelListener wageExcelListener;

    //表格样式
    private static CellStyle headStyle;
    private static CellStyle titleStyle;
    private static CellStyle bodyStyle;
    private static CellStyle numStyle;
    private static CellStyle sumStyle;

    //导出基础模板
    @Override
    public void downloadBaseExcel(HttpServletRequest request, HttpServletResponse response) {
        //位置默认
        String path = "D:\\MyCloudSpace\\我的坚果云\\SpringBoot+SpringCloud深度整合-新\\1、Template\\boot-template\\boot-template-single\\src\\main\\java\\com\\lhz\\project\\wage\\导入模板\\基础数据.xlsx";
        FileUtil.downloadFile(new File(path), request, response, "基础数据.xlsx");
    }

    @Override
    public List<WageBase> listWageBase() {
        return wageBaseMapper.listWageBase();
    }


    @Override
    public List<WageWeal> listWageWeal() {
        return wageWealMapper.listWageWeal();
    }


    /**
     * 上传excel
     *
     * @param file
     * @throws Exception
     */
    @Override
    public void uploadBaseExcel(MultipartFile file) throws Exception {
        //设置headRowNumber表示从第几行开始读取，下标从0开始
        EasyExcel.read(file.getInputStream(), WageBaseData.class, wageExcelListener).sheet().headRowNumber(2).doRead();
    }

    //对导入数据进行业务判断
    @Override
    public void checkBaseExcel(WageBaseData base, Integer rowIndex) {
        if (StrUtil.isEmpty(base.getName()) || StrUtil.isEmpty(base.getJoinDate())) {
            throw new ServiceException(500, "第" + rowIndex + "行，数据不能为空");
        }
    }

    /**
     * 保存基础工资数据
     *
     * @param baseDataList
     */
    @Override
    @Transactional
    public void saveBaseExcel(List<WageBaseData> baseDataList) {
        //插入数据库
        for (WageBaseData baseData : baseDataList) {
            WageBase wageBase = new WageBase();
            BeanUtils.copyProperties(baseData, wageBase);
            wageBase.setId(UUIDUtils.getUuId());
            wageBaseMapper.insert(wageBase);
        }

    }

    /**
     * 上传excel
     *
     * @param file
     * @throws Exception
     */
    @Override
    public void uploadWealExcel(MultipartFile file) throws Exception {
        //设置headRowNumber表示从第几行开始读取，下标从0开始
        EasyExcel.read(file.getInputStream(), WageWealData.class, wageExcelListener).sheet().headRowNumber(2).doRead();
    }

    //对导入数据进行业务判断
    @Override
    public void checkWealExcel(WageWealData wealData, Integer rowIndex) {
        if (StrUtil.isEmpty(wealData.getName()) || StrUtil.isEmpty(wealData.getIdCard())) {
            throw new ServiceException(500, "第" + rowIndex + "行，数据不能为空");
        }
    }

    /**
     * 保存福利工资数据
     *
     * @param wealDataList
     */
    @Override
    @Transactional
    public void saveWealExcel(List<WageWealData> wealDataList) {
        //插入数据库
        for (WageWealData wealData : wealDataList) {
            WageWeal wageWeal = new WageWeal();
            BeanUtils.copyProperties(wealData, wageWeal);
            wageWeal.setId(UUIDUtils.getUuId());
            wageWealMapper.insert(wageWeal);
        }

    }

    /**
     * 导出工资表
     */
    @Override
    public void outWageExcel(HttpServletResponse response) {

        //创建表格
        Workbook workbook = new XSSFWorkbook();
        //创建样式
        headStyle = ExcelUtil.setDataAllStyle(workbook, "head");
        //设置数据标题样式
        titleStyle = ExcelUtil.setDataAllStyle(workbook, "title");
        //设置数据单元格全局样式
        bodyStyle = ExcelUtil.setDataAllStyle(workbook, "body");
        //设置数字样式
        numStyle = ExcelUtil.setDataAllStyle(workbook, "num");
        //设置合计样式
        sumStyle = ExcelUtil.setDataAllStyle(workbook, "sum");
        //设置sheet
        //测试地址：http://127.0.0.1:10008/demo/wageBase/out
        Sheet sheetBase = workbook.createSheet("基本工资");
        Sheet sheetWeal = workbook.createSheet("福利工资");
        Sheet sheetAll = workbook.createSheet("工资汇总");
        setBaseSheet(sheetBase);
        setWealSheet(sheetWeal);
        setTotalSheet(sheetAll);
        //设置输出的文件名称
        String fileName = "工资统计.xlsx";
        //输出
        ExcelUtil.outExcel(workbook, fileName, response);
    }

    private void setBaseSheet(Sheet sheet) {
        List<WageBase> bases = wageBaseMapper.listWageBase();
        //定义列名
        String header = "基本工资";

        //二级标题(占完所有列)
        String[] title = {"序号", "姓名", "身份证号", "银行卡号", "入职时间", "职务", "年薪", "绩效", "基础工资"};
        //从第3行开始为body内容
        int baseRow = 2;
        //单元格总大小
        int size = title.length;

        //特殊单元格列号及大小(col[start-end]:num)
        String[] specialCols = {"0:10", "2-3:40", "4:20", "6-11:20"};

        //设置列宽规则(有一个集合存放特殊单元格宽(col:num)，和一个默认宽度)
        Map<String, Object> colMap = new HashMap<>();
        colMap.put("special", specialCols);
        colMap.put("def", 15);

        //设置单元格
        //设置统一的列宽
        Row row = ExcelUtil.setColumnWidth(sheet, size, colMap);
        //创建第一行表头标题及设置单元格宽度
        ExcelUtil.setHeader(row, header, sheet, headStyle, size);
        //创建单元格标题
        ExcelUtil.setTitleOneLevel(sheet, titleStyle, title);

        //将列数据放到list集合中
        List<List<Object>> valueList = getBaseRowValueList(bases);
        //新增数据行，并且设置单元格数据(统一设置)，从第3行开始写入数据
        List<Row> rowList = ExcelUtil.setRowValue(valueList, baseRow, sheet, bodyStyle);

        //根据业务需求对单元格样式进行单独处理(比如加一列用于合计，对某些列设置保留小数)
        setBaseNumRowValue(rowList, numStyle);
    }

    private void setWealSheet(Sheet sheet) {
        List<WageWeal> weals = wageWealMapper.listWageWeal();
        //定义列名
        String header = "福利工资";

        //二级标题(占完所有列)
        String[] title = {"序号", "姓名", "身份证号", "交通费", "通讯费", "保密费"};
        //从第3行开始为body内容
        int baseRow = 2;
        //单元格总大小
        int size = title.length;

        //特殊单元格列号及大小(col[start-end]:num)
        String[] specialCols = {"0:10", "2:40", "3-5:20"};

        //设置列宽规则(有一个集合存放特殊单元格宽(col:num)，和一个默认宽度)
        Map<String, Object> colMap = new HashMap<>();
        colMap.put("special", specialCols);
        colMap.put("def", 15);

        //设置单元格
        //设置统一的列宽
        Row row = ExcelUtil.setColumnWidth(sheet, size, colMap);
        //创建第一行表头标题及设置单元格宽度
        ExcelUtil.setHeader(row, header, sheet, headStyle, size);
        //创建单元格标题
        ExcelUtil.setTitleOneLevel(sheet, titleStyle, title);

        //将列数据放到list集合中
        List<List<Object>> valueList = getWealRowValueList(weals);
        //新增数据行，并且设置单元格数据(统一设置)，从第3行开始写入数据
        List<Row> rowList = ExcelUtil.setRowValue(valueList, baseRow, sheet, bodyStyle);

        //根据业务需求对单元格样式进行单独处理(比如加一列用于合计，对某些列设置保留小数)
        setWealNumRowValue(rowList, numStyle);
    }

    private void setTotalSheet(Sheet sheet) {
        List<WageOut> list = wageBaseMapper.listWageOutData();
        //定义列名
        String header = "工资汇总";
        //创建标题
        //一级标题，规则(name:colNum:colspan)
        String[] oneTitle = {"工资:6:3", "福利:9:3"};
        //二级标题(占完所有列)
        String[] twoTitle = {"序号", "姓名", "身份证号", "银行卡号", "入职时间", "职务", "年薪", "绩效", "基础工资", "交通费", "通讯费", "保密费", "实发金额"};
        //从第4行开始为body内容
        int baseRow = 3;

        //单元格总大小
        int size = twoTitle.length;

        //特殊单元格列号及大小(col[start-end]:num)
        String[] specialCols = {"0:10", "2-3:40", "4:20", "6-11:20"};

        //设置列宽规则(有一个集合存放特殊单元格宽(col:num)，和一个默认宽度)
        Map<String, Object> colMap = new HashMap<>();
        colMap.put("special", specialCols);
        colMap.put("def", 15);

        //设置sheet属性
        //冻结行列
        /*
         * cellNum:表示要冻结的列数；2
         * rowNum:表示要冻结的行数；3
         * firstCellNum:表示被固定列右边第一列的列号；2
         * firstRollNum :表示被固定行下边第一列的行号; 3
         */
        sheet.createFreezePane(2, 3, 2, 3);

        //设置单元格
        //设置统一的列宽
        Row row = ExcelUtil.setColumnWidth(sheet, size, colMap);
        //创建第一行表头标题及设置单元格宽度
        ExcelUtil.setHeader(row, header, sheet, headStyle, size);
        //创建单元格标题
        ExcelUtil.setTitleTwoLevel(size, sheet, titleStyle, oneTitle, twoTitle);

        //将列数据放到list集合中
        List<List<Object>> valueList = getAllRowValueList(list);
        //新增数据行，并且设置单元格数据(统一设置)，从第3行开始写入数据
        List<Row> rowList = ExcelUtil.setRowValue(valueList, baseRow, sheet, bodyStyle);

        //根据业务需求对单元格样式进行单独处理(比如加一列用于合计，对某些列设置保留小数)
        setTotalNumRowValue(rowList, numStyle);
        setTotalSumRowValue(baseRow, rowList, sheet, size, sumStyle);
    }

    private List<List<Object>> getBaseRowValueList(List<WageBase> dataList) {
        List<List<Object>> list = new ArrayList<>();
        int i = 1;
        for (WageBase data : dataList) {
            List<Object> objs = new ArrayList<>();
            objs.add(i); //序号
            objs.add(data.getName());
            objs.add(data.getIdCard());
            objs.add(data.getBankCard());
            objs.add(data.getJoinDate());
            objs.add(data.getPosition());
            objs.add(data.getAnnualSalary());
            objs.add(data.getPerformance());
            objs.add(data.getBaseWage());
            list.add(objs);
            i++;
        }
        return list;
    }

    private List<List<Object>> getWealRowValueList(List<WageWeal> dataList) {
        List<List<Object>> list = new ArrayList<>();
        int i = 1;
        for (WageWeal data : dataList) {
            List<Object> objs = new ArrayList<>();
            objs.add(i); //序号
            objs.add(data.getName());
            objs.add(data.getIdCard());
            objs.add(data.getTraffic());
            objs.add(data.getCommunication());
            objs.add(data.getSecret());
            list.add(objs);
            i++;
        }
        return list;
    }

    private List<List<Object>> getAllRowValueList(List<WageOut> dataList) {
        List<List<Object>> list = new ArrayList<>();
        int i = 1;
        for (WageOut data : dataList) {
            List<Object> objs = new ArrayList<>();
            objs.add(i); //序号
            objs.add(data.getName());
            objs.add(data.getIdCard());
            objs.add(data.getBankCard());
            objs.add(data.getJoinDate());
            objs.add(data.getPosition());
            objs.add(data.getAnnualSalary());
            objs.add(data.getPerformance());
            objs.add(data.getBaseWage());
            objs.add(data.getTraffic());
            objs.add(data.getCommunication());
            objs.add(data.getSecret());
            objs.add(data.getBaseWage() + data.getTraffic() + data.getCommunication() + data.getSecret());
            list.add(objs);
            i++;
        }
        return list;
    }

    private static void setBaseNumRowValue(List<Row> rowList, CellStyle numStyle) {
        //对特定的单元格设置新的样式
        for (Row row : rowList) {
            for (int i = 6; i <= 8; i++) {
                row.getCell(i).setCellStyle(numStyle);
            }
        }
    }

    private static void setWealNumRowValue(List<Row> rowList, CellStyle numStyle) {
        //对特定的单元格设置新的样式
        for (Row row : rowList) {
            for (int i = 3; i <= 5; i++) {
                row.getCell(i).setCellStyle(numStyle);
            }
        }
    }

    private static void setTotalNumRowValue(List<Row> rowList, CellStyle numStyle) {
        //对特定的单元格设置新的样式
        for (Row row : rowList) {
            for (int i = 6; i <= 11; i++) {
                row.getCell(i).setCellStyle(numStyle);
            }
        }
    }


    private static void setTotalSumRowValue(int startRow, List<Row> rowList, Sheet sheet, int size, CellStyle sumStyle) {
        String nameValue = "合计";
        //设置行数
        int lastRow = startRow + rowList.size();
        //建立总计行
        Row countRow = sheet.createRow(lastRow);
        for (int i = 0; i < size; i++) {
            Cell countCell = countRow.createCell(i);
            countCell.setCellValue(nameValue);
            countCell.setCellStyle(sumStyle);
            if (i > 5) {
                //设置公式
                countCell = countRow.createCell(i);//设置公式前，一定要先建立表格
                String colString = CellReference.convertNumToColString(i); //长度转成ABC列
                String sumString = "SUM(" + colString + startRow + ":" + colString + lastRow + ")";//求和公式s
                countCell.setCellFormula(sumString);
                countCell.setCellStyle(sumStyle);
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(lastRow, lastRow, 0, 5));
    }
}